package com.baihui.taobao.db;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.PropertyUtils;

import com.baihui.taobao.util.Util;
import com.taobao.api.TaobaoObject;

/**
 * Created by Kevin. User: HSZM1106-0312 Date: 12-1-7 Time: 上午10:38
 */
public abstract class GeneDAO {

	public static String[] excludefield = new String[] { "serialVersionUID" };
	private static String emptyString = "  ";

	/**
	 * 批量插入淘宝对象
	 * 
	 * @param infos
	 * @throws Exception
	 */
	public void insert(List infos) throws Exception {
		insert(infos, null);
	}

	public void insert(List<TaobaoObject> infos, Connection cn)
			throws Exception {
		if (infos == null || infos.size() <= 0)
			return;
		List<Object[]> values = new ArrayList<Object[]>();
		Object info = infos.get(0);
		Field[] descriptors = Util.getAllField(info.getClass());
		for (Object taobaoObject : infos) {
			if (info == null)
				info = taobaoObject;
			List<Object> tmp = constructValues((TaobaoObject) taobaoObject,
					descriptors, true);
			values.add(tmp.toArray(new Object[] {}));
		}

		updateAndInsert(cn, generateInsertSql(info.getClass(), descriptors),
				values);
	}

	public void update(List<TaobaoObject> infos, Connection cn)
			throws Exception {
		if (infos == null || infos.size() <= 0)
			return;
		List<Object[]> values = new ArrayList<Object[]>();
		Object info = infos.get(0);
		Field[] descriptors = Util.getAllField(info.getClass());
		for (Object taobaoObject : infos) {
			if (info == null)
				info = taobaoObject;
			List<Object> tmp = constructValues((TaobaoObject) taobaoObject,
					descriptors, false);
			values.add(tmp.toArray(new Object[] {}));
		}

		updateAndInsert(cn, generateUpdateSql(info.getClass(), descriptors),
				values);
	}

	/**
	 * 插入某个淘宝对象到数据库中
	 * 
	 * @param info
	 * @throws Exception
	 */
	public void insert(TaobaoObject info) throws Exception {
		Field[] descriptors = Util.getAllField(info.getClass());
		List<Object> values = constructValues(info, descriptors, true);
		updateAndInsert(generateInsertSql(info.getClass(), descriptors),
				values.toArray(new Object[] {}));
	}

	/**
	 * 根据SQL语句查看是否存在
	 * 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public boolean isExist(String sql) throws Exception {
		Connection cn = null;
		java.sql.PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			cn = DBConnectionMgr.getConnection();
			ps = cn.prepareStatement(sql);
			rs = ps.executeQuery();
			return rs.next();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			releaseConnection(cn, ps, rs);
		}
	}

	public boolean isExist(String sql, Object[] params) throws Exception {
		Connection cn = null;
		java.sql.PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			cn = DBConnectionMgr.getConnection();
			ps = cn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject((i + 1), params[i]);
				}
			}
			rs = ps.executeQuery();
			return rs.next();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			releaseConnection(cn, ps, rs);
		}
	}

	/**
	 * 更新或者新增
	 * 
	 * @param sql
	 * @param params
	 * @throws Exception
	 */
	public void updateAndInsert(String sql, Object[] params) throws Exception {
		Connection cn = null;
		java.sql.PreparedStatement ps = null;
		try {
			cn = DBConnectionMgr.getConnection();
			if (cn != null) {
				ps = cn.prepareStatement(sql);
				if (params != null && params.length > 0) {
					for (int i = 0; i < params.length; i++) {
						ps.setObject((i + 1), params[i]);
					}
				}
				ps.execute();
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			releaseConnection(cn, ps, null);
		}
	}

	public void updateAndInsert(List<String> sqls, Connection cn)
			throws Exception {
		if (sqls == null || sqls.size() <= 0)
			return;
		// 是否外部传入连接
		boolean hasConnection = (cn != null);
		java.sql.Statement ps = null;
		try {
			if (!hasConnection) {
				cn = DBConnectionMgr.getConnection();
			}
			if (cn != null) {
				ps = cn.createStatement();
				for (String sql : sqls) {
					ps.addBatch(sql);
				}
				ps.executeBatch();
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			releaseConnection((!hasConnection ? cn : null), ps, null);
		}
	}

	public void updateAndInsert(Connection cn, String sql, List<Object[]> values)
			throws Exception {
		if (values == null || values.size() <= 0)
			return;
		// 是否外部传入连接
		boolean hasConnection = (cn != null);
		java.sql.PreparedStatement ps = null;
		try {
			if (!hasConnection) {
				cn = DBConnectionMgr.getConnection();
			}
			if (cn != null) {
				ps = cn.prepareStatement(sql);

				for (Object[] params : values) {
					if (params != null && params.length > 0) {
						for (int i = 0; i < params.length; i++) {
							ps.setObject((i + 1), params[i]);
						}
					}
					ps.addBatch();
				}
				ps.executeBatch();
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			releaseConnection((!hasConnection ? cn : null), ps, null);
		}
	}

	/**
	 * 批量插入数据
	 * 
	 * @param sql
	 *            sql语句
	 * @param values
	 *            多条记录对应的VALUE值
	 * @throws Exception
	 */
	public void updateAndInsert(String sql, List<Object[]> values)
			throws Exception {
		updateAndInsert(null, sql, values);
	}

	/**
	 * 查找某个数据库对象，对象存储在MAP中 返回多个
	 * 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public List<Map> findAllObject(String sql) throws Exception {
		List ret = new ArrayList();
		Connection cn = null;
		java.sql.PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			cn = DBConnectionMgr.getConnection();
			ps = cn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Map map = new HashMap();
				ResultSetMetaData metaData = ps.getMetaData();
				int count = metaData.getColumnCount();
				for (int i = 0; i < count; i++) {
					String columnName = metaData.getColumnName(i + 1);
					map.put(columnName, rs.getObject(columnName));
				}
				ret.add(map);
			}

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			releaseConnection(cn, ps, rs);
		}
		return ret;
	}

	public List<Map> findAllObject(String sql, Object[] params)
			throws Exception {
		List ret = new ArrayList();
		Connection cn = null;
		java.sql.PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			cn = DBConnectionMgr.getConnection();
			ps = cn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject((i + 1), params[i]);
				}
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				Map map = new HashMap();
				ResultSetMetaData metaData = ps.getMetaData();
				int count = metaData.getColumnCount();
				for (int i = 0; i < count; i++) {
					String columnName = metaData.getColumnName(i + 1);
					map.put(columnName, rs.getObject(columnName));
				}
				ret.add(map);
			}

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			releaseConnection(cn, ps, rs);
		}
		return ret;
	}
	
	public List<String> findListString(String sql, Object[] params)
	throws Exception {
		List<String> ret = new ArrayList<String>();
		Connection cn = null;
		java.sql.PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			cn = DBConnectionMgr.getConnection();
			ps = cn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject((i + 1), params[i]);
				}
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				ResultSetMetaData metaData = ps.getMetaData();
				int count = metaData.getColumnCount();
				for (int i = 0; i < count; i++) {
					String columnName = metaData.getColumnName(i + 1);
					ret.add(String.valueOf(rs.getObject(columnName)));
				}
			}
		
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			releaseConnection(cn, ps, rs);
		}
		return ret;
		}

	public Map findObject(String sql, Object[] params) throws Exception {
		Connection cn = null;
		java.sql.PreparedStatement ps = null;
		ResultSet rs = null;
		Map map = new HashMap();
		try {
			cn = DBConnectionMgr.getConnection();
			if (cn != null) {
				ps = cn.prepareStatement(sql);
				if (params != null && params.length > 0) {
					for (int i = 0; i < params.length; i++) {
						ps.setObject((i + 1), params[i]);
					}
				}
				rs = ps.executeQuery();
				while (rs.next()) {

					ResultSetMetaData metaData = ps.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = 0; i < count; i++) {
						String columnName = metaData.getColumnName(i + 1);
						map.put(columnName, rs.getObject(columnName));
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			releaseConnection(cn, ps, null);
		}
		return map;
	}

	public Object findObject(String sql, Object[] params, Object object)
			throws Exception {
		Connection cn = null;
		java.sql.PreparedStatement ps = null;
		ResultSet rs = null;
		// Map map = new HashMap();
		// 通过传来的对象获取Class
		Class<?> classType = object.getClass();
		Method[] ms = classType.getMethods();
		// 生成user2对象
		// Object ob = classType.getConstructor(new Class[]{}).newInstance(new
		// Object[]{});
		Object ob = null;
		Field[] fields = classType.getDeclaredFields();
		try {
			cn = DBConnectionMgr.getConnection();
			if (cn != null) {
				ps = cn.prepareStatement(sql);
				if (params != null && params.length > 0) {
					for (int i = 0; i < params.length; i++) {
						ps.setObject((i + 1), params[i]);
					}
				}
				rs = ps.executeQuery();
				while (rs.next()) {
					// ResultSetMetaData metaData = ps.getMetaData();
					// int count = metaData.getColumnCount();
					ob = classType.newInstance();
					for (int i = 0; i < fields.length; i++) {
						Field field = fields[i];
						// String columnName = metaData.getColumnName(i + 1);
						String fieldName = field.getName();
						String firstChar = fieldName.substring(0, 1)
								.toUpperCase();
						// String getterName = "get" + firstChar +
						// fieldName.substring(1);
						String setterName = "set" + firstChar
								+ fieldName.substring(1);
						for (Method m : ms) {
							if (setterName.equals(m.getName())) {
								m.invoke(ob, rs.getObject(fieldName));// 调用set方法,将该列的值放到set方法当中去.
								break;
							}
						}

//						Method getter = classType.getMethod(getterName);
//						Method setter = classType.getMethod(setterName,
//								new Class[] { field.getType() });
//						// map.put(columnName, rs.getObject(columnName));
//						// 执行getter方法获取当前域的值
//						Object result = getter.invoke(rs);
//						// 执行setter给user2赋值
//						setter.invoke(ob, new Object[] { result });
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			releaseConnection(cn, ps, null);
		}
		return ob;
	}

	/**
	 * 查找某个数据库对象，对象存储在MAP中 返回多个
	 * 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public List<Map> findAllObject(String sql, int maxCount) throws Exception {
		List ret = new ArrayList();
		Connection cn = null;
		java.sql.PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			cn = DBConnectionMgr.getConnection();
			ps = cn.prepareStatement(sql);
			rs = ps.executeQuery();
			int num = 0;
			while (rs.next()) {
				if (num <= maxCount) {
					Map map = new HashMap();
					ResultSetMetaData metaData = ps.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = 0; i < count; i++) {
						String columnName = metaData.getColumnName(i + 1);
						map.put(columnName, rs.getObject(columnName));
					}
					ret.add(map);
					num++;
				} else {
					break;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			releaseConnection(cn, ps, rs);
		}
		return ret;
	}

	/**
	 * 查找某个数据库对象，对象存储在MAP中 返回一个
	 * 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public Map<String, Object> findObject(String sql) throws Exception {
		Map<String, Object> map = new HashMap<String, Object>();
		Connection cn = null;
		java.sql.PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			cn = DBConnectionMgr.getConnection();
			ps = cn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				ResultSetMetaData metaData = ps.getMetaData();
				int count = metaData.getColumnCount();
				for (int i = 0; i < count; i++) {
					String columnName = metaData.getColumnName(i + 1);
					map.put(columnName, rs.getObject(columnName));
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			releaseConnection(cn, ps, rs);
		}
		return map;
	}

	/**
	 * 释放链接
	 * 
	 * @param cn
	 * @param ps
	 * @param rs
	 * @throws Exception
	 */
	protected void releaseConnection(Connection cn, PreparedStatement ps,
			ResultSet rs) throws Exception {
		try {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
			if (cn != null)
				cn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	private void releaseConnection(Connection cn, Statement ps, ResultSet rs)
			throws Exception {
		try {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
			if (cn != null)
				cn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	/**
	 * 根据某个字段和值来查询是否存在
	 * 
	 * @param fieldValue
	 * @param fieldName
	 * @return
	 * @throws Exception
	 */
	public boolean isExist(Object fieldValue, String fieldName, String tableName)
			throws Exception {
		if (tableName == null || tableName.length() <= 0)
			return false;
		String sql = null;
		if (fieldValue instanceof String) {
			sql = "select " + fieldName + " from " + tableName + " where "
					+ fieldName + " = '" + fieldValue + "' ";
		} else if (fieldValue instanceof Integer) {
			sql = "select " + fieldName + " from " + tableName + " where  "
					+ fieldName + "=" + fieldValue;
		}
		return isExist(sql);
	}

	/**
	 * 根据某个字段和值来查询对象
	 * 
	 * @param fieldValue
	 * @param fieldName
	 * @return
	 * @throws Exception
	 */
	public Map find(Object fieldValue, String fieldName, String tableName)
			throws Exception {
		if (tableName == null || tableName.length() <= 0)
			return null;
		String sql = null;
		if (fieldValue instanceof String) {
			sql = "select * from " + tableName + " where " + fieldName + " = '"
					+ fieldValue + "' ";
		} else if (fieldValue instanceof Integer || fieldValue instanceof Long) {
			sql = "select * from " + tableName + " where  " + fieldName + "="
					+ fieldValue;
		}
		return findObject(sql);
	}

	/**
	 * 生成插入的SQL语句
	 * 
	 * @param domainClass
	 * @return
	 * @throws Exception
	 */
	public String generateInsertSql(Class domainClass, Field[] fields)
			throws Exception {
		try {
			String formateReg = "insert into {0}({1}) values({2})";

			String tableName = "T" + domainClass.getSimpleName();
			StringBuilder fieldParam = new StringBuilder();
			StringBuilder valueParam = new StringBuilder();

			for (int i = 0; i < fields.length; i++) {
				Field field = fields[i];
				if (Util.isExist(field.getName(), getExcludefield())) {
					continue;
				}
				if (i < fields.length - 1) {
					fieldParam.append(field.getName() + ",");
					valueParam.append("?,");
				} else {
					fieldParam.append(field.getName());
					valueParam.append("?");
				}
			}
			MessageFormat format = new MessageFormat(formateReg);
			return format.format(new String[] { tableName,
					fieldParam.toString(), valueParam.toString() });
		} catch (Exception e) {
			throw e;
		}
	}

	/**
	 * 生成插入的SQL语句
	 * 
	 * @param domainClass
	 * @param fields
	 * @return
	 * @throws Exception
	 */
	public String generateUpdateSql(Class domainClass, Field[] fields)
			throws Exception {
		try {
			String formateReg = "update {0} set {1} where {2}";

			String tableName = "T" + domainClass.getSimpleName();
			StringBuilder fieldParam = new StringBuilder();
			StringBuilder valueParam = new StringBuilder();

			for (int i = 0; i < fields.length; i++) {
				Field field = fields[i];
				if (Util.isExist(field.getName(), getExcludefield())) {
					continue;
				}
				if (field.getName().equals(getPrimaryKey(domainClass))) {
					continue;
				}
				if (field.getName().equals("id"))
					continue;
				if (i < fields.length - 1) {
					fieldParam.append(field.getName() + "=?,");
				} else {
					fieldParam.append(field.getName() + "=?");
				}
			}
			valueParam.append((getPrimaryKey(domainClass) == null ? "id"
					: getPrimaryKey(domainClass)) + "=?");
			MessageFormat format = new MessageFormat(formateReg);
			return format.format(new String[] { tableName,
					fieldParam.toString(), valueParam.toString() });
		} catch (Exception e) {
			throw e;
		}
	}

	/**
	 * 根据业务不同在这里补充，如果没有则会主动建立主键
	 * 
	 * @param aClass
	 * @return
	 */
	protected abstract String getPrimaryKey(Class aClass);

	/**
	 * 生成创建表的对象
	 */
	public String geneCreateTableSql() throws Exception {
		StringBuilder sqls = new StringBuilder();
		try {
			Class aClass = getDomainClass();
			sqls.append(" DROP TABLE IF EXISTS T" + aClass.getSimpleName()
					+ ";\n");
			sqls.append(" CREATE TABLE  T" + aClass.getSimpleName()).append(
					" ( \n");

			if (getPrimaryKey(aClass) == null) {
				sqls.append(emptyString
						+ " id int(11) NOT NULL AUTO_INCREMENT,\n");
			}

			Field[] descriptors = Util.getAllField(aClass);
			for (int i = 0; i < descriptors.length; i++) {
				Field field = descriptors[i];
				if (Util.isExist(field.getName(), getExcludefield())) {
					continue;
				}
				String type = "varchar(255)";
				if (field.getType().getSimpleName().toUpperCase()
						.equals("LONG")) {
					type = "bigint(50)";
				} else if (field.getType().getSimpleName().toUpperCase()
						.equals("BOOLEAN")) {
					type = "int(4)";
				} else if (field.getType().getSimpleName().toUpperCase()
						.equals("DATE")) {
					type = "datetime";
				} else if (field.getType().getSimpleName().toUpperCase()
						.equals("LIST")) {
					type = "int(11)";
				} else if (field.getType().getSimpleName().toUpperCase()
						.equals("STRING")) {
					type = "varchar(255)";
				} else if (field.getType().getSuperclass()
						.equals(TaobaoObject.class)) {
					type = parseRelationType(field);
				} else {
					System.out.println(aClass.getName() + "-->"
							+ field.getName());
				}
				sqls.append(emptyString + " " + field.getName() + " " + type
						+ " ,\n");
			}
			sqls.append(emptyString
					+ " PRIMARY KEY ("
					+ (getPrimaryKey(aClass) == null ? "id"
							: getPrimaryKey(aClass)) + ") \n");
			sqls.append(" ); \n");
		} catch (Exception e) {
			throw e;
		}
		return sqls.toString();
	}

	/**
	 * 返回对应的INFO类，根据这个类来创建表，生成SQL语句，Update语句，插入和更新数据。
	 * 
	 * @return
	 */
	protected abstract Class getDomainClass();

	/**
	 * 创建表时，如果存在关联属性，关联属性的类型需要在这里返回，在生成CRETE语句时使用.
	 * 
	 * @param field
	 * @return
	 */
	protected abstract String parseRelationType(Field field);

	/**
	 * 从info对象中构造PreparedStatement中的值
	 * 
	 * @param info
	 * @return
	 * @throws Exception
	 */
	protected List<Object> constructValues(TaobaoObject info,
			Field[] descriptors, boolean insert) throws Exception {
		List<Object> values = new ArrayList<Object>();
		for (int i = 0; i < descriptors.length; i++) {
			Field field = descriptors[i];
			if (Util.isExist(field.getName(), getExcludefield())) {
				continue;
			}
			if (!insert) {
				if (field.getName().equals(getPrimaryKey(info.getClass()))) {
					continue;
				}
			}
			try {
				Object o = PropertyUtils.getProperty(info, field.getName());
				values.add(parseValue(o, field));
			} catch (IllegalAccessException e) {
				throw new Exception(e);
			} catch (InvocationTargetException e) {
				throw new Exception(e);
			} catch (NoSuchMethodException e) {
				throw new Exception(e);
			}
		}
		if (!insert) {
			values.add(PropertyUtils.getProperty(info,
					getPrimaryKey(info.getClass())));
		}
		return values;
	}

	/**
	 * 构造SQL语句的VALUE值时使用，关联属性一般需要处理下。
	 * 
	 * @param value
	 * @param field
	 * @return
	 */
	protected abstract Object parseValue(Object value, Field field);

	/**
	 * 子类可以覆盖，决定哪些属性不插入到数据库中
	 * 
	 * @return
	 */
	public String[] getExcludefield() {
		return excludefield;
	}
}
